CREATE TABLE IF NOT EXISTS [ods_table_name] (
   `ods_id` STRING COMMENT '公共字段-代理主键'
  ,`source` STRING COMMENT '公共字段-来源系统或平台'
  ,`source_table` STRING COMMENT '公共字段-来源表'
  ,`etl_time` STRING COMMENT '公共字段-入库时间戳到秒'
{{#each columns}}  
  ,`{{this.name}}` {{this.type}} COMMENT '{{this.display}}'
{{/each}}  
) COMMENT '{{display}}' PARTITIONED BY (ds STRING COMMENT '时间分区yyyyMMdd') LIFECYCLE 7;

INSERT  OVERWRITE TABLE [ods_table_name] PARTITION (ds = '${bizdate}')
SELECT
    md5(concat(
      {{#each columns}}
      {{#unless @first}}, {{/unless}}`{{this.name}}`
      {{/each}}
    )) AS ods_id -- 公共字段-代理主键
  , '订单系统' AS source -- 公共字段-来源系统或平台
  , '[stg_table_name]' AS source_table -- 公共字段-来源表
  , TO_CHAR(GETDATE() ,'yyyymmddhhmiss') AS etl_time -- 公共字段-入库时间戳到秒
  {{#each columns}}
  , CAST(`{{this.name}}` AS {{this.type}}) AS `{{this.name}}` -- {{this.display}}
  {{/each}}
FROM (
  SELECT
      row_number() over(partition by [t1.id] order by [t1.ts] desc) AS rn,
    {{#each columns}}
    , `{{this.name}}` -- {{this.display}}
    {{/each}}    
  FROM (
    SELECT
      {{#each columns}}
      {{#unless @first}}, {{/unless}}CAST(`{{this.name}}` AS {{this.type}}) AS `{{this.name}}` -- {{this.display}}
      {{/each}}
    FROM [stg_table_name]
    WHERE ds = '${bizdate}' --当天增量
    UNION
    SELECT
      {{#each columns}}
      {{#unless @first}}, {{/unless}}`{{this.name}}` -- {{this.display}}
      {{/each}}
    FROM [ods_table_name]
    WHERE ds = '${bizdate-1}' --前一天全量
  ) 
) t2
WHERE t2.rn = 1